﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SapB1WebApp.Utils;
using System.Data.SqlClient;
using System.Data;

namespace SapB1WebApp.DAL
{
    public class SAPConnection
    {
        private SqlConnection sConnSAP;
        private SAPbobsCOM.Company oCompany;

        public string setConnectionStringDB(string sapUserName, string sapPassWord, string companyDB)
        {
            try
            {
                string SQLUser = null;
                string SQLPwd = null;
                string SQLServer = null;
                string LicenseServer = null;
                string SQLVersion = null;
                string strConnect = string.Empty;
                string sCon = string.Empty;
                string SQLType = string.Empty;
                string[] connectStringArr;
                string sErrMsg = string.Empty;
                strConnect = "DBConnect";
                sCon = System.Configuration.ConfigurationManager.AppSettings.Get(strConnect);
                connectStringArr = sCon.Split(';');

                if (connectStringArr.Length > 0)
                {
                    SQLServer = connectStringArr[0].ToString();
                    SQLUser = connectStringArr[1].ToString();
                    SQLPwd = connectStringArr[2].ToString();
                    LicenseServer = connectStringArr[3].ToString();
                    SQLVersion = connectStringArr[4].ToString();

                    // Select Company
                    sCon = "server= " + SQLServer + ";database=" + companyDB + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";";
                    sConnSAP = new SqlConnection(sCon);

                    oCompany = new SAPbobsCOM.Company();
                    oCompany.CompanyDB = companyDB;
                    oCompany.UserName = sapUserName;
                    oCompany.Password = sapPassWord;
                    oCompany.Server = SQLServer;
                    oCompany.DbUserName = SQLUser;
                    oCompany.DbPassword = SQLPwd;
                    oCompany.LicenseServer = LicenseServer;
                    if (SQLVersion == "2008")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008;
                    }
                    else if (SQLVersion == "2012")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012;
                    }
                    else if (SQLVersion == "2005")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return ex.Message;
            }
            return string.Empty;
        }
        public string connectDBForLogin()
        {
            try
            {
                string sErrMsg = string.Empty;
                int connectOk = 0;
                if (oCompany.Connected == false)
                {
                    if (oCompany.Connect() != 0)
                    {
                        oCompany.GetLastError(out connectOk, out sErrMsg);
                        Log.WriteError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, Utils.Utils.GetCurrentMethod(), "SAPError", sErrMsg);
                        return sErrMsg;
                    }
                    return string.Empty;
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
            }
            return string.Empty;
        }
        public SAPbobsCOM.Company connectDB()
        {
            try
            {
                string sErrMsg = string.Empty;
                int connectOk = 0;
                if (oCompany.Connected == false)
                {
                    if (oCompany.Connect() != 0)
                    {
                        oCompany.GetLastError(out connectOk, out sErrMsg);
                        Log.WriteError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, Utils.Utils.GetCurrentMethod(), "SAPError", sErrMsg);
                        return null;
                    }
                    return oCompany;
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
            }
            return null;
        }
        public double GetSAPItemPrice(string sapUserName, string sapPassword, string companyDB, string cardCode, string itemCode, float amount, DateTime refDate)
        {
            try
            {
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    SAPbobsCOM.SBObob vObj = default(SAPbobsCOM.SBObob);
                    SAPbobsCOM.Recordset rs = default(SAPbobsCOM.Recordset);

                    vObj = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge);
                    rs = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                    rs = vObj.GetItemPrice(cardCode, itemCode, amount, refDate);
                    if (rs.RecordCount > 0)
                    {
                        return rs.Fields.Item(0).Value;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return 0;
            }
            finally
            {
                if (oCompany.Connected)
                {
                    oCompany.Disconnect();
                }
            }
            return 0;
        }

        public DataTable CreateMarketingDocument(string sapUserName, string sapPassWord, string companyDB, string strXml, string docType, string key, bool isUpdate)
        {
            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            try
            {
                dynamic oDocment = null;
                switch (docType)
                {
                    case "30":
                        oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                        break;
                    case "97":
                        oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                        break;
                    case "191":
                        oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                        break;
                    case "33":
                        oDocment = (SAPbobsCOM.Contacts)oDocment;
                        break;
                    case "221":
                        oDocment = (SAPbobsCOM.Attachments2)oDocment;
                        break;
                    case "2":
                        oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                        break;
                    case "28":
                        oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                        break;
                    default:
                        oDocment = (SAPbobsCOM.Documents)oDocment;
                        break;
                }

                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassWord,companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    oCompany.XMLAsString = true;
                    oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0);
                    if (isUpdate)
                    {
                        if (oDocment.GetByKey(key))
                        {
                            oDocment.Browser.ReadXML(strXml, 0);
                            lErrCode = oDocment.Update();
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                        }
                    }
                    else
                    {
                        lErrCode = oDocment.Add();
                    }

                    if (lErrCode != 0)
                    {
                        oCompany.GetLastError(out lErrCode, out  sErrMsg);
                        return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                    }
                    else
                    {
                        if (!isUpdate)
                        {
                            string newDocEntry = oCompany.GetNewObjectKey();
                            return Utils.Utils.ReturnMessage(lErrCode, newDocEntry);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(lErrCode, ex.Message);
            }
            finally
            {
                if (oCompany.Connected)
                {
                    oCompany.Disconnect();
                }
            }
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public DataTable CloseCancelDocument(string sapUserName, string sapPassWord,string companyDB, string docType, string key, bool isCancel)
        {
            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            try
            {
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassWord, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    dynamic oDocment = null;

                    switch (docType)
                    {
                        case "30":
                            oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                            break;
                        case "97":
                            oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                            break;
                        case "191":
                            oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                            break;
                        case "33":
                            oDocment = (SAPbobsCOM.Contacts)oDocment;
                            break;
                        case "221":
                            oDocment = (SAPbobsCOM.Attachments2)oDocment;
                            break;
                        case "2":
                            oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                            break;
                        case "28":
                            oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                            break;
                        case "23":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations);
                            break;
                        case "17":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
                            break;
                        case "15":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
                            break;
                        case "13":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices);
                            break;
                        case "18":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices);
                            break;
                        case "24":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments);
                            break;
                        case "22":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
                            break;
                        case "20":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes);
                            break;
                        case "67":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
                            break;
                        case "1250000001":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
                            break;
                        case "112":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts);
                            break;
                        default:
                            oDocment = (SAPbobsCOM.Documents)oDocment;
                            break;
                    }
                    if (oDocment.GetByKey(key))
                    {
                        if (isCancel)
                        {
                            if (docType == ConvertDocType.Get2String(DocType.GOODS_RECEIPT_PO))
                            {
                                SAPbobsCOM.Documents ocanclleddoc;
                                ocanclleddoc = oDocment.CreateCancellationDocument();
                                lErrCode = ocanclleddoc.Add();
                            }
                            else
                            {
                                lErrCode = oDocment.Cancel();
                            }
                        }
                        else
                        {
                            lErrCode = oDocment.Close();
                        }
                        if (lErrCode != 0)
                        {
                            oCompany.GetLastError(out lErrCode, out  sErrMsg);
                            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, key);
                        }
                    }
                    else
                    {
                        return Utils.Utils.ReturnMessage(lErrCode, "Can not found document.");
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(lErrCode, ex.Message);
            }
            finally
            {
                if (oCompany.Connected)
                {
                    oCompany.Disconnect();
                }
            }
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public DataTable DeleteMarketingDocument(string sapUserName, string sapPassWord,string companyDB, DataTable tblLine, string docType, string key)
        {
            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            string tableLineName = string.Empty;
            try
            {
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassWord, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    dynamic oDocment = null;

                    switch (docType)
                    {
                        case "30":
                            oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                            break;
                        case "97":
                            oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                            break;
                        case "191":
                            oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                            break;
                        case "33":
                            oDocment = (SAPbobsCOM.Contacts)oDocment;
                            break;
                        case "221":
                            oDocment = (SAPbobsCOM.Attachments2)oDocment;
                            break;
                        case "2":
                            oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                            break;
                        case "28":
                            oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                            break;
                        case "23":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations);
                            tableLineName = "QUT1";
                            break;
                        case "17":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
                            tableLineName = "RDR1";
                            break;
                        case "15":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
                            break;
                        case "13":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices);
                            break;
                        case "18":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices);
                            break;
                        case "24":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments);
                            break;
                        case "22":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
                            tableLineName = "POR1";
                            break;
                        case "20":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes);
                            tableLineName = "PDN1";
                            break;
                        case "67":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
                            break;
                        case "1250000001":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
                            break;
                        case "112":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts);
                            break;
                        default:
                            oDocment = (SAPbobsCOM.Documents)oDocment;
                            break;
                    }
                    if (oDocment.GetByKey(key))
                    {
                        SAPbobsCOM.Recordset linesRs = default(SAPbobsCOM.Recordset);
                        linesRs = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                        linesRs.DoQuery(string.Format("SELECT LineNum FROM {0} WHERE DocEntry = {1} ORDER BY LineNum", tableLineName, key));
                        if (oDocment.GetByKey(key))
                        {
                            bool isDeleted = false;
                            int deletedCount = 0;
                            if (tblLine != null)
                            {
                                foreach (DataRow row in tblLine.Rows)
                                {
                                    int lineIndex = 0;
                                    linesRs.MoveFirst();
                                    lineIndex = lineIndex - deletedCount;
                                    while (linesRs.EoF == false)
                                    {
                                        if (linesRs.Fields.Item("LineNum").Value == int.Parse(row["LineNum"].ToString()))
                                        {
                                            isDeleted = true;
                                            break;
                                        }
                                        else
                                        {
                                            isDeleted = false;
                                        }
                                        lineIndex = lineIndex + 1;
                                        linesRs.MoveNext();
                                    }
                                    if (isDeleted)
                                    {
                                        oDocment.Lines.SetCurrentLine(lineIndex);
                                        oDocment.Lines.Delete();
                                        deletedCount = deletedCount + 1;
                                    }
                                }
                                lErrCode = oDocment.Update();
                                if (lErrCode != 0)
                                {
                                    oCompany.GetLastError(out lErrCode, out  sErrMsg);
                                    return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                                }
                                else
                                {
                                    return Utils.Utils.ReturnMessage(lErrCode, key);
                                }
                            }
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(-1, "Record not found!");
                        }
                    }
                    else
                    {
                        return Utils.Utils.ReturnMessage(-1, "Can not found document.");
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(-1, ex.Message);
            }
            finally
            {
                if (oCompany.Connected)
                {
                    oCompany.Disconnect();
                }
            }
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
    }
}